package com.phoenix.jsef.code.generator.provider;

import com.phoenix.jsef.code.generator.pojo.ColumnSchema;
import com.phoenix.jsef.code.generator.pojo.PrimaryKeySchema;
import com.phoenix.jsef.code.generator.pojo.TableSchema;
import com.phoenix.jsef.code.generator.template.TemplateMethodForGetDataType;
import com.phoenix.jsef.code.generator.template.TemplateMethodForUnderlineToCamel;
import com.phoenix.jsef.common.CUtils;
import com.phoenix.jsef.framework.FillEntityHelper;
import com.phoenix.jsef.jdbcutils.CommandType;
import com.phoenix.jsef.jdbcutils.DataBaseType;
import com.phoenix.jsef.jdbcutils.SqlHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.util.*;

@Service
public class MySQLSchemaProvider implements IDbSchemaProvider {

	@Autowired
	@Qualifier("sqlHelper")
	private SqlHelper sqlHelper;
	
	@Autowired
	private TemplateMethodForGetDataType getDataTypeTemplateMethod;
	
	@Autowired
	private TemplateMethodForUnderlineToCamel underlineToCamelTemplateMethod;
	
	private String databaseName = "";

    public DataBaseType dataBaseType(){
        return DataBaseType.MYSQL;
    }

    @Override
	public String getDatabaseName() {
		if (databaseName.equals("")) {
			try {
				List<Map<String, Object>> result = sqlHelper.getResultList(CommandType.Text," SELECT DATABASE() AS DATABASE_NAME ");
				Iterator iteratorRows = result.iterator();
				while (iteratorRows.hasNext()) {
					Map<String, Object> map = (Map<String, Object>) iteratorRows.next();
					databaseName = CUtils.getMapValueByKeyIgnoreCase(map, "DATABASE_NAME").toString();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return databaseName;
	}

	@Override
	public List<TableSchema> getTables() {
		String sql = String.format( "SELECT TABLE_NAME, '' OWNER, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s' AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') ORDER BY 1", getDatabaseName());
		List<TableSchema> tableList = new ArrayList<>();
		try {
			List<Map<String, Object>> result = sqlHelper.getResultList(CommandType.Text,sql);

            Iterator iteratorRows = result.iterator();
			while (iteratorRows.hasNext()) {
				Map<String, Object> map = (Map<String, Object>) iteratorRows.next();
				tableList.add((TableSchema)FillEntityHelper.fillEntity(map, TableSchema.class, ""));
			}		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return tableList;
	}

	@Override
	public List<ColumnSchema> getTableColumns(String tableName) {
		String sql = String.format(
				"SELECT COLUMN_NAME,\r\n" + 
				"       CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE,\r\n" + 
				"       DATA_TYPE,\r\n" + 
				"       CASE WHEN DATA_TYPE IN('CHAR', 'VARCHAR', 'BINARY', 'VARBINARY', 'NCHAR', 'NVARCHAR') THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END  `LENGTH`,\r\n" + 
				"       COLUMN_COMMENT,\r\n" + 
				"       TABLE_NAME\r\n" + 
				"FROM INFORMATION_SCHEMA.COLUMNS\r\n" + 
				"WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'\r\n" + 
				"ORDER BY ORDINAL_POSITION ",
				getDatabaseName(), tableName);
		List<ColumnSchema> colList = new ArrayList<>();
		try {
			List<Map<String, Object>> result = sqlHelper.getResultList(CommandType.Text, sql);
			Iterator iteratorRows = result.iterator();
			while (iteratorRows.hasNext()) {
				Map<String, Object> map = (Map<String, Object>) iteratorRows.next();
				colList.add((ColumnSchema)FillEntityHelper.fillEntity(map, ColumnSchema.class, ""));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return colList;
	}

	@Override
	public List<PrimaryKeySchema> getTablePrimaryKey(String tableName) {
		String sql = String.format(
				"SELECT t1.CONSTRAINT_NAME, \r\n" + 
				"       t1.COLUMN_NAME,\r\n" + 
				"       t1.TABLE_NAME\r\n" + 
				"FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1\r\n" + 
				"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2\r\n" + 
				"ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA\r\n" + 
				"AND t2.TABLE_NAME = t1.TABLE_NAME\r\n" + 
				"AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME\r\n" + 
				"WHERE t1.TABLE_SCHEMA = '%s' AND t1.TABLE_NAME = '%s'\r\n" + 
				"AND t2.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n" + 
				"ORDER BY t1.ORDINAL_POSITION",
				getDatabaseName(), tableName);
		List<PrimaryKeySchema> pkList = new ArrayList<>();	
		try {
			List<Map<String, Object>> result = sqlHelper.getResultList(CommandType.Text, sql);
			Iterator iteratorRows = result.iterator();
			while (iteratorRows.hasNext()) {
				Map<String, Object> map = (Map<String, Object>) iteratorRows.next();
				pkList.add((PrimaryKeySchema)FillEntityHelper.fillEntity(map, PrimaryKeySchema.class, ""));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if(pkList.size()<1) {//如果没有定义主键就指定该表第一个字段为主键
			List<ColumnSchema> colList = getTableColumns(tableName);
			pkList.add(new PrimaryKeySchema(colList.get(0).getColumnName(), colList.get(0).getTableName()));
		}
		
		return pkList;
	}
	
	@Override
	public String getDataType(String type) {
		String t = type.toUpperCase();
		switch (t) {
		case "VARCHAR":
		case "CHAR":
		case "TEXT":
			return "String";
		case "BLOB":
			return "byte[]";
		case "INT":
			return "Long";
		case "BIGINT":
			return "BigInteger";
		case "TINYINT":
		case "SMALLINT":
		case "MEDIUMINT":
			return "Integer";
		case "BIT":
		case "BOOLEAN":
		case "BOOL":
			return "Boolean";
		case "FLOAT":
			return "Float";
		case "DOUBLE":
			return "Double";
		case "DECIMAL":
			return "java.math.BigDecimal";
			
		case "DATETIME":
		case "TIMESTAMP":
		case "DATE":
			return "Date";

		default:
			return "Object";
		}
	}
	
	/**
	 * 准备好渲染到模板上的数据
	 * @param tableName 数据库表名称
	 * @return
	 */
	@Override
	public Map<String,Object> prepareDataModel(String tableName){
		Map<String, Object> dataModel = new LinkedHashMap<String, Object>();
		dataModel.put("sourceTable", tableName);
		dataModel.put("columns", getTableColumns(tableName));
		dataModel.put("primaryKey",getTablePrimaryKey(tableName));
		
		dataModel.put("underlineToCamel", underlineToCamelTemplateMethod);
		dataModel.put("getDataType", getDataTypeTemplateMethod);
		
		return dataModel;	
	}

}
